/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package helper;

import dbconnector.DBDriver;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author FRIENDS
 */

public class Announcement
{
    static PreparedStatement p,pstmt;
    static Statement s;
    static ResultSet rs;
    
public static AO.Announcement getAnnouncementBean(String announceid)
{
    try{
         p= DBDriver.getPrepare("select * from announcements where announceid=?");
         p.setString(1, announceid);
         rs=p.executeQuery();
         AO.Announcement announcementBean=new AO.Announcement();
         if(rs.next())
         {
             announcementBean.setAnnounceid(rs.getString("announceid"));
             announcementBean.setAnnouncetype(rs.getString("announcetype"));
             announcementBean.setCatagory(rs.getString("catagory"));
             announcementBean.setTousers(rs.getString("tousers"));
             announcementBean.setAuthor(rs.getString("author"));
             announcementBean.setSubject(rs.getString("subject"));
             announcementBean.setDescription(rs.getString("description"));
             announcementBean.setCourseid(rs.getString("courseid"));
             announcementBean.setAnnouncedate(rs.getString("announcedate"));
           }
         return announcementBean;
}catch(Exception e){e.printStackTrace();return null;}
    
}

public static String[] getAnnouncementIds(String type)
{
    try
    {  if(type.equals("all"))
    {p=DBDriver.getPrepare("select announceid from announcements order by announcedate desc");pstmt=DBDriver.getPrepare("select 1 from announcements");}
    else if(type.equals("public")) 
    {p=DBDriver.getPrepare("select announceid from announcements where announcetype='public' order by announcedate desc");pstmt=DBDriver.getPrepare("select 1 from announcements where announcetype='public'");}
    else if(type.equals("private"))
    {p=DBDriver.getPrepare("select announceid from announcements where announcetype='private' order by announcedate desc");pstmt=DBDriver.getPrepare("select 1 from announcements where announcetype='private'");}
    else {return null;}
        rs=p.executeQuery();
        int i=0,j=0;
        
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] announceIds=new String[j];
         while(rs.next())
        {
            announceIds[i++]=rs.getString("announceid");
         }
        return announceIds;
    }catch(Exception e){return null ;}
    
}


public static String[] getUserAnnouncementIds(String userid)
{
    try
    {
        p=DBDriver.getPrepare("select announceid from announcements where author=? order by announcedate desc");
        p.setString(1, userid);
        rs=p.executeQuery();
        int i=0,j=0;
        pstmt=DBDriver.getPrepare("select 1 from announcements where author=?");
        pstmt.setString(1, userid);
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] announceIds=new String[j];
        while(rs.next())
        {
            announceIds[i++]=rs.getString("announceid");
        }
        return announceIds;
        
    }catch(Exception e){return null;}
}


public static String getNewAnnouncementId()
{
    try
    {
        p=DBDriver.getPrepare("select max(announceid) from announcements");
        rs=p.executeQuery();
        if(rs.next())
            if(rs.getString(1)!=null)
            {   
              int i=Integer.parseInt(rs.getString(1).substring(3));
               return "ann"+(++i);
            }
         }
        
    catch(Exception e){return "ann1"; }
    return "ann1";
   
}

public static boolean announceToUsers(AO.Announcement a)
{
    
    String type= a.getAnnouncetype();
    String tousers=a.getTousers();
    
    if(type.equals("public"))
        return true;
    String ids[]=null;
    if(tousers.equals("all"))
    ids=helper.Login.getUserids("users");
    else
        ids=helper.course.getCourseUserIds(tousers);
    try{
    if(ids!=null)
    {
    Connection con=new DBDriver().getCon();
    con.setAutoCommit(false);
    s=con.createStatement();
    
    
    for(int i=0;i<ids.length;i++)
               s.addBatch("insert into userannouncelist values('"+ids[i]+"','"+a.getAnnounceid()+"',0)");               
    s.executeBatch();
    con.commit();
    con.rollback();
    con.close();
    return true;
    }
        return false;
    }
    
    catch(Exception e){e.printStackTrace(); return false;}
     
    
}

public static String[] getMyAnnouncementIds(String userid, boolean checktype)
{
    try
    {  if(!checktype)
    {
        p=DBDriver.getPrepare("select userannouncelist.announceid from userannouncelist inner join announcements where userannouncelist.userid=? and userannouncelist.ischecked=0 order by announcements.announcedate desc");
        p.setString(1, userid);
        
        
        pstmt=DBDriver.getPrepare("select 1 from userannouncelist inner join announcements where userannouncelist.userid=? and userannouncelist.ischecked=0");
        pstmt.setString(1, userid);
       
    }
    else
    {
        p=DBDriver.getPrepare("select userannouncelist.announceid from userannouncelist inner join announcements where userannouncelist.userid=? and announcements.announceid=userannouncelist.announceid  order by announcements.announcedate desc");
        p.setString(1, userid);
       
        
        pstmt=DBDriver.getPrepare("select 1 from userannouncelist inner join announcements where userannouncelist.userid=? and announcements.announceid=userannouncelist.announceid ");
        pstmt.setString(1, userid);
       
    }
        rs=p.executeQuery();
        int i=0,j=0;
     
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] announceIds=new String[j];
        while(rs.next())
        {
            announceIds[i++]=rs.getString("announceid");
        }
        return announceIds;
       
    }catch(Exception e){e.printStackTrace();return null;}
 
   
}


public static void main(String[] a)
{
    String[] ids=getMyAnnouncementIds("shekar",true);
    for(int i=0;i<ids.length;i++)
    {
        System.out.println(ids[i]);
    }
    AO.Announcement ann=new AO.Announcement();
    ann=helper.Announcement.getAnnouncementBean("ann3");
    System.out.println(ann.getCourseid());
}
}
